from django.shortcuts import render,HttpResponse

from readxls.models import Exam,Kaoti,KaotiForm

import  xlrd
import os
import json
# Create your views here.
#D:\10机器学习\机器学习学习资料\题库\机器学习1\201812考题\第1周周考\2018_12人工智能(机器学习1)_周考_01_理论.xls
def writeToDB(xlspath):
    basename = os.path.basename(xlspath)    #获取文件名
    workbook = xlrd.open_workbook(xlspath)  #打开文件
    print(workbook.sheet_names())

    sheet1 = workbook.sheet_by_index(0) #获取工作表
    print(sheet1.name,sheet1.nrows,sheet1.ncols)

    kaoti = Kaoti(name=basename)
    kaoti.save()
    for i in range(1,sheet1.nrows):
        # Exam(*sheet1.row_values(i),kaoti=kaoti).save()
        try:
            Exam(
                bianhao = i,
                kaoti = kaoti,
                zhishidian = sheet1.row_values(i)[0],
                leixing    = sheet1.row_values(i)[1],
                tigan      = sheet1.row_values(i)[2],
                xxa        = sheet1.row_values(i)[3],
                xxb        = sheet1.row_values(i)[4],
                xxc        = sheet1.row_values(i)[5],
                xxd        = sheet1.row_values(i)[6],
                xxe        = sheet1.row_values(i)[7],
                xxf        = sheet1.row_values(i)[8],
                daan       = sheet1.row_values(i)[9],
                jiexi      = sheet1.row_values(i)[10],
                chutiren   = sheet1.row_values(i)[11],
            ).save()
        except Exception as e:
            print('报错信息：',e)

    print('read success')

def query(path):#获取所有存放xls文件的路径
    all_xls_paths = []
    for i in os.walk(path):
        # print(i)
        if len(i[1])==0:
            for j in i[2]:
                # print(j)
                if j.strip().endswith('.xls') or j.strip().endswith('.xlsx'):
                    all_xls_paths.append(os.path.join(i[0],j))
    # print(len(set(all_xls_paths)),set(all_xls_paths))
    for i in all_xls_paths:
        writeToDB(i)


def show(request):
    # path = r'D:\10机器学习\机器学习学习资料\题库'  # 存放文件的路径
    # path = r'D:\10机器学习\机器学习学习资料\题库\机器学习1\201811考题'  # 存放文件的路径
    # # path = r'D:\5RIA'  # 存放文件的路径
    # query(path)
    if request.method == 'GET':

        kaotiform = KaotiForm()
        # return render(request, 'readxls/showExams.html', locals())
        return render(request, 'readxls/showExams.html', {'kaotiform': kaotiform})
    else:
        post = request.POST
        kaoti_id = post.get('id').strip()

        print(kaoti_id)
        # exams = Exam.objects.values_list(kaoti_id = kaoti_id)
        exams = list(Exam.objects.filter(kaoti_id = kaoti_id).values())
        print(exams)

        return HttpResponse(json.dumps(exams))
        # return HttpResponse('')

